from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from StuManSys import app

# 先初始化一个db实例用于创建数据库
db = SQLAlchemy(app)


class UserData(db.Model):
    __tablename__ = 'user_data'
    email = db.Column(db.String(30), primary_key=True, nullable=False)
    password = db.Column(db.String(60), nullable=False)
    birth_date = db.Column(db.Date)


class Student(db.Model):
    __tablename__ = 'students'

    id = db.Column(db.Integer, primary_key=True)
    student_id = db.Column(db.String(20), unique=True, nullable=False)
    name = db.Column(db.String(100), nullable=False)
    gender = db.Column(db.String(10))
    birth_date = db.Column(db.Date)
    class_name = db.Column(db.String(50))
    admission_date = db.Column(db.Date)
    contact_phone = db.Column(db.String(20))
    email = db.Column(db.String(100))

    scores = db.relationship('Score', backref='student', lazy=True)

    def __repr__(self):
        return f'<Student {self.name}>'


class Course(db.Model):
    __tablename__ = 'courses'

    id = db.Column(db.Integer, primary_key=True)
    course_code = db.Column(db.String(20), unique=True, nullable=False)
    name = db.Column(db.String(100), nullable=False)
    credit = db.Column(db.Float)
    teacher = db.Column(db.String(100))

    scores = db.relationship('Score', backref='course', lazy=True)

    def __repr__(self):
        return f'<Course {self.name}>'


class Score(db.Model):
    __tablename__ = 'scores'

    id = db.Column(db.Integer, primary_key=True)
    student_id = db.Column(db.Integer, db.ForeignKey('students.id'), nullable=False)
    course_id = db.Column(db.Integer, db.ForeignKey('courses.id'), nullable=False)
    score = db.Column(db.Float)
    semester = db.Column(db.String(20))
    academic_year = db.Column(db.String(10))

    def __repr__(self):
        return f'<Score {self.score}>'
# 删除并重新创建数据库（仅运行一次）
def create_db():
    with app.app_context():
        # 删除数据库（如果存在）
        db.session.execute("DROP DATABASE IF EXISTS StudentsDB")
        db.session.commit()

        # 创建数据库
        db.session.execute("CREATE DATABASE StudentsDB")
        db.session.commit()

        # 重新配置应用使用新创建的数据库
        app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root@localhost/StudentsDB'

        # 重新初始化SQLAlchemy以使用新的数据库
        db.init_app(app)

        # 创建所有表
        db.create_all()

        # 使用正确的数据库连接执行查询
        with db.engine.connect() as connection:
            # 打印数据库中的表
            result = connection.execute("SHOW TABLES")
            tables = result.fetchall()
            print("数据库中的表:")
            for table in tables:
                print(table[0])

            # 打印表结构
            for table in tables:
                table_name = table[0]
                print(f"\n表 {table_name} 的结构:")
                result = connection.execute(f"DESCRIBE {table_name}")
                columns = result.fetchall()
                for column in columns:
                    print(
                        f"字段名: {column[0]}, 类型: {column[1]}, 是否为空: {column[2]}, 键: {column[3]}, 默认值: {column[4]}")


if __name__ == '__main__':
    create_db()